package com.fis.hthk.dblinkapi.dblinkImpl;




import com.agileitp.forte.framework.Array;
import com.agileitp.forte.framework.TextData;
import com.fis.hthk.dblinkapi.util.ExecProcedure;
import com.fis.hthk.dblinkapi.util.WmsWebuiValidationDeleteImpl;
import com.fis.hthk.dblinkapi.util.WmsWebuiValidationInsertImpl;
import com.fis.hthk.dblinkapi.util.WmsWebuiValidationUpdateImpl;
import com.ssaglobal.SsaException;
import com.ssaglobal.scm.wms.service.baseobjects.EXEDataObject;
import com.ssaglobal.scm.wms.service.exeprocessmanager.TransactionServiceSORemote;
import com.ssaglobal.scm.wms.service.exeprocessmanager.TransactionServiceSORemoteHome;
import com.ssaglobal.util.EjbHelper;




public class dblinkImpl {


	/**
	 * Get key values
	 * @param jndi String e.g. WMWHSE1
	 * @param userid String e.g. admin
	 * @param keyname String e.g. movekey
	 * @param fieldlength String length of key to be returned e.g. 10
	 * @param increment String the increment of the ncouter e.g. 1
	 * @return String the key with leading zero
	 * @throws Exception
	 */
	public static String getKey(String provider, String jndi, String userid, String keyname,
			String fieldlength, String increment) throws Exception {

		String rtnString = new String();
		String procedureName = "NSPG_GETKEY";
		Array parms1 = new Array();
		parms1.add(new TextData(keyname));
		parms1.add(new TextData(fieldlength));
		parms1.add(new TextData(increment));
		EXEDataObject result;
		try {
			result = ExecProcedure.execProcedureAction(provider, jndi, userid, procedureName,
					parms1);
			rtnString = result.getAttribValue(new TextData("keyname")).getAsString();
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		}

		return rtnString;

	}	


	/**
	 * insert into MoveHeader
	 * 
	 * @param db_connection String e.g. WHTHK_wmwhse1
	 * @param wmWhseID String e.g. wmwhse1
	 * @param provider String e.g. 192.168.205.153:11499
	 * @param MoveKey String
	 * @param StorerKey String
	 * @param FromFacility String
	 * @param ToFacility String
	 * @param FromLoc String
	 * @param ToLoc String
	 * @param Status String
	 * @param RMAKey String nullable
	 * @param Reason String
	 * @param InternalReturnNo String nullable
	 * @return 
	 * @throws Exception 
	 */
	public static void MoveHeaderInsert(String db_connection, String wmWhseID, String provider, 
			String MoveKey, String StorerKey, String FromFacility, String ToFacility, String FromLoc, String ToLoc, 
			String Status, String RMAKey, String Reason, String InternalReturnNo ) throws Exception {


		String sql = "INSERT INTO MOVEHEADER ( MoveKey, StorerKey, FromFacility, ToFacility, FromLoc, ToLoc, Status, RMAKey, Reason, InternalReturnNo ) VALUES ( '" 
				+ MoveKey + "', '" + StorerKey + "', '" + FromFacility + "', '" + ToFacility + "', '" + FromLoc + "', '" + ToLoc + "', '" 
				+ Status + "', '" + RMAKey + "', '" + Reason + "', '" + InternalReturnNo + "')";

		WmsWebuiValidationInsertImpl.insert(sql, db_connection, wmWhseID, provider);

	}

	/**
	 * insert into MoveDetail
	 * 
	 * @param db_connection String e.g. WHTHK_wmwhse1
	 * @param wmWhseID String e.g. wmwhse1
	 * @param provider String e.g. 192.168.205.153:11499
	 * @param MoveKey String
	 * @param MoveLineNumber String
	 * @param Sku String
	 * @param Lot String
	 * @param FromLoc String
	 * @param ToLoc String
	 * @param Qty String
	 * @param QtyDifference String nullable
	 * @param DropID String
	 * @param FromID String
	 * @param TOID String
	 * @param MSN String nullable
	 * @param PickDetailKey String nullable
	 * @return 
	 * @throws Exception
	 */
	public static void MoveDetailInsert(String db_connection, String wmWhseID, String provider, String MoveKey, String MoveLineNumber, 
			String StorerKey, String Sku, String Lot, String FromLoc, String ToLoc, 
			String Qty, String QtyDifference, String DropID , String FromID, String TOID, String MSN, String PickDetailKey) throws Exception {

		String sql = "INSERT INTO MoveDetail ( MoveKey, MoveLineNumber, Sku, StorerKey, Lot, FromLoc, ToLoc, Qty, QtyDifference, DropID, TOID, FromID, MSN, PickDetailKey ) VALUES ( '" 
				+ MoveKey + "', '" + MoveLineNumber + "', '" + Sku + "', '" + StorerKey + "', '" + Lot + "', '" + FromLoc + "', '" + ToLoc + "', " 
				+ Qty + ", " + QtyDifference + ", '" + DropID  + "', '" + TOID + "', '" + FromID + "', '" + MSN + "', '" + PickDetailKey + "' ) ";

		WmsWebuiValidationInsertImpl.insert(sql, db_connection, wmWhseID, provider);

	}


	/**
	 * update movedetail (edit MSN)
	 * 
	 * @param db_connection String e.g. WHTHK_wmwhse1
	 * @param wmWhseID String e.g. wmwhse1
	 * @param provider String e.g. 192.168.205.153:11499
	 * @param MoveKey String  
	 * @param MoveLineNumber String
	 * @param Sku String  
	 * @param MSN String
	 * @return 
	 * @throws Exception
	 */
	public static void MoveDetailUpdate(String db_connection, String wmWhseID, String provider, String MoveKey, String MoveLineNumber, String Sku,  String MSN ) throws Exception {

		String sql = "UPDATE MoveDetail SET MSN = '" + MSN + "' WHERE MoveKey = '" + MoveKey + "' AND Sku = '" + Sku + "' AND MoveLineNumber = '" + MoveLineNumber + "'";

		WmsWebuiValidationUpdateImpl.update(sql, db_connection, wmWhseID, provider);

	}


	/**
	 * update moveheader
	 * 
	 * @param db_connection String e.g. WHTHK_wmwhse1
	 * @param wmWhseID String e.g. wmwhse1
	 * @param provider String e.g. 192.168.205.153:11499
	 * @param Status String
	 * @return 
	 * @throws Exception
	 */
	public static void MoveHeaderUpdate(String db_connection, String wmWhseID, String provider, String MoveKey , String Status) throws Exception {

		String sql = "UPDATE MoveHeader SET Status = '" + Status + "' WHERE MoveKey = '" + MoveKey + "'";

		WmsWebuiValidationUpdateImpl.update(sql, db_connection, wmWhseID, provider);

	}


	/**
	 * Delete from MoveDetail
	 * 
	 * @param db_connection String e.g. WHTHK_wmwhse1
	 * @param wmWhseID String e.g. wmwhse1
	 * @param provider String e.g. 192.168.205.153:11499
	 * @param MoveKey String
	 * @param MoveLineNumber String
	 * @return 
	 * @throws Exception
	 */
	public static void MoveDetailDelete(String db_connection, String wmWhseID, String provider, String MoveKey, String MoveLineNumber) throws Exception {

		String sql = "DELETE FROM MoveDetail WHERE MoveKey = '" + MoveKey + "' AND MoveLineNumber = '" + MoveLineNumber + "'";

		WmsWebuiValidationDeleteImpl.delete(sql, db_connection, wmWhseID, provider);
	}

	/**
	 * Delete from MoveHeader
	 * 
	 * @param db_connection String e.g. WHTHK_wmwhse1
	 * @param wmWhseID String e.g. wmwhse1
	 * @param provider String e.g. 192.168.205.153:11499
	 * @param MoveKey String
	 * @return 
	 * @throws Exception
	 */
	public static void MoveHeaderDelete(String db_connection, String wmWhseID, String provider, String MoveKey)  throws Exception {

		String sql = "DELETE FROM MoveHeader WHERE MoveKey = '" + MoveKey + "'";

		WmsWebuiValidationDeleteImpl.delete(sql, db_connection, wmWhseID, provider);
	}



	public static void main(String []args){
		String db_connection = "WHTHK_wmwhse1", wmWhseID = "wmwhse1", provider = "192.168.205.153:11499", MoveKey = "10000000000", StorerKey = "HTHK", FromFacility = "Test", ToFacility = "Test", FromLoc = "PICKTO", ToLoc = "U1", Status = "0", RMAKey = "", Reason = "1", InternalReturnNo = "";

		try {
			dblinkImpl.MoveHeaderInsert(db_connection, wmWhseID, provider, MoveKey, StorerKey, FromFacility, ToFacility, FromLoc, ToLoc, Status, RMAKey, Reason, InternalReturnNo);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}
